﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace winformLesson1
{

    /// <summary>
    /// 数据库帮助类
    /// </summary>
    public class SqlHelper
    {
        public static string connStr = System.Configuration.ConfigurationManager.AppSettings["connStr"];


        /// <summary>
        /// 判断是否存在
        /// </summary>
        /// <param name="sql">Sql 语句</param>
        /// <param name="paras">防Sql注入参数</param>
        /// <returns></returns>
        public static bool Exist(string sql, SqlParameter[] paras)
        {
            int r = (int)ExecuteScalar(sql, paras);
            return r == 0 ? false : true;
        }


        /// <summary>
        /// 根据sql语句，查出第一行第一列数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand comm = new SqlCommand(sql, conn))
                {
                    if (paras != null)
                    {
                        comm.Parameters.AddRange(paras);
                    }
                    conn.Open();
                    return comm.ExecuteScalar();
                }
            }
        }


        /// <summary>
        /// 执行sql语句，返回受影响得行数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="paras">防sql对象</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(string sql, SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand comm = new SqlCommand(sql, conn))
                {
                    if (paras != null)
                    {
                        comm.Parameters.AddRange(paras);
                    }
                    conn.Open();
                    return comm.ExecuteNonQuery();
                }
            }
        }




        /// <summary>
        /// 根据sql语句，获取实体
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static T GetModel<T>(string sql, SqlParameter[] paras)
        {
           
            using (SqlConnection conn = new SqlConnection(connStr))
            using (SqlCommand comm = new SqlCommand(sql, conn))
            {
                using (IDataReader reader = ExecuteReader(conn, comm, paras))
                {
                    if (reader.Read())
                    {
                        // 转成一个实体类，返回
                        return Reflection.ReaderToModel<T>(reader);
                    }
                    else
                    {
                        return default(T);
                    }
                }
            }
        }

        public static List<T> GetList<T>(string sql, SqlParameter[] paras)
        {
            List<T> data = new List<T>();
            using (SqlConnection conn = new SqlConnection(connStr))
            using (SqlCommand comm = new SqlCommand(sql, conn))
            {
                using (IDataReader reader = ExecuteReader(conn, comm, paras))
                {
                    while (reader.Read())
                    {
                        data.Add(Reflection.ReaderToModel<T>(reader));
                    }
                }
            }
            return data;
        }

        public static IDataReader ExecuteReader(SqlConnection conn, SqlCommand comm,string sql, SqlParameter[] paras)
        {
            return ExecuteReader(conn, comm, paras);
        }

        public static SqlDataReader ExecuteReader(SqlConnection conn, SqlCommand comm, SqlParameter[] paras)
        {
            comm.Parameters.AddRange(paras);
            conn.Open();
            // 这里不能进行释放，如果这里释放掉了，后面使用会报错，阅读器关闭时尝试调用 Read 无效。
            return comm.ExecuteReader();
        }

        /// <summary>
        /// 根据sql语句 获取结果集
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTableCollection GetTable(string sql, SqlParameter[] paras)
        {
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(connStr))
            using (SqlCommand comm = new SqlCommand(sql, conn))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = comm;
                adapter.Fill(ds);
            }

            DataTableCollection dtl = ds.Tables;
            return dtl;
        }



    }
}